﻿/*
 * 文件名：Sub.cs
 * 类名：XHCTMS.DAL.Sub
 * 创建人：朱震宇
 * 创建时间：2013.5.21
 * 
 * 修改人：朱震宇
 * 修改时间：2013.5.22
 * 修改内容：添加方法
 * 
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace XHCTMS.DAL
{
    public class Sub
    {
        /// <summary>
        /// 专业增加
        /// </summary>
        /// <param name="sub">专业信息</param>
        /// <returns>返回类型int 成功/1 失败/0 异常/-1</returns>
        public int Add(XHCTMS.MODEL.Sub sub)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("insert into sub values(@sub_name,@sub_years,@sub_desc,default)");
            SqlParameter[] para ={
                                     new SqlParameter("@sub_name",SqlDbType.NVarChar,32),
                                     new SqlParameter("@sub_years",SqlDbType.Int),
                                     new SqlParameter("@sub_desc",SqlDbType.NVarChar,128),                                     
                                };
            para[0].Value = sub.SubName;
            para[1].Value = sub.SubYears;
            para[2].Value = sub.SubDesc;

            return new XHCTMS.DBHELP.Common().ExcuteQuery(queryString.ToString(), para);
        }

        /// <summary>
        /// 查看所有专业信息
        /// </summary>
        /// <returns>返回类型DataTable 异常/null</returns>
        public DataTable GetSub()
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("select sub_id,sub_name,sub_years,sub_desc from sub");
            return new XHCTMS.DBHELP.Common().GetDataTable(queryString.ToString());
        }



        /// <summary>
        /// 获取所有专业名
        /// </summary>
        /// <returns>返回类型DataTable 异常/null</returns>
        public DataTable GetSubName()
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("select sub_id, sub_name from sub where sub_flag =1  order by sub_name");
            return new XHCTMS.DBHELP.Common().GetDataTable(queryString.ToString());
        }


        /// <summary>
        /// 获得所有不重复专业学制
        /// </summary>
        /// <returns></returns>
        public DataTable GetSubYears()
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("select distinct sub_years from sub where sub_flag =1  order by sub_years ");
            return new XHCTMS.DBHELP.Common().GetDataTable(queryString.ToString());
        }


        /// <summary>
        /// 查找专业信息
        /// </summary>
        /// <param name="sub">专业名称</param>
        /// <returns>返回类型DataTable 异常/null</returns>
        public DataTable GetName(MODEL.Sub sub)
        {
            StringBuilder quersyString = new StringBuilder();
            quersyString.Append("select * from sub where sub_name=@sub_name and sub_years=@sub_years");
            SqlParameter[] paras ={
                                      new SqlParameter("@sub_name",SqlDbType.NVarChar,32),
                                      new SqlParameter("@sub_years",SqlDbType.Int)
                                  };
            paras[0].Value = sub.SubName;
            paras[1].Value = sub.SubYears;

            return new XHCTMS.DBHELP.Common().GetDataTable(quersyString.ToString(), paras);
        }

        /// <summary>
        /// 通过专业编号修改专业是否存在 存在/1 不存在/0
        /// </summary>
        /// <param name="sub">专业编号</param>
        /// <returns>返回类型int 成功/1 失败/0 异常/-1</returns>
        public int AlterId(MODEL.Sub sub)
        {
            StringBuilder quersyString = new StringBuilder();
            quersyString.Append("update sub set sub_flag=@sub_flag where sub_id=@sub_id");
            SqlParameter[] paras ={
                                     new SqlParameter("@sub_flag",SqlDbType.Bit),
                                     new SqlParameter("@sub_id",SqlDbType.Int)
                                 };
            paras[0].Value = sub.SubFlag;
            paras[1].Value = sub.SubId;

            return new XHCTMS.DBHELP.Common().ExcuteQuery(quersyString.ToString(), paras);
        }

        /// <summary>
        /// 删除专业信息
        /// </summary>
        /// <param name="sub">专业编号</param>
        /// <returns>返回类型int 成功/1 失败/0 异常/-1</returns>
        public int DelId(int subID)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("update sub set sub_flag=0 where sub_id=@sub_id");
            SqlParameter[] paras ={
                                      new SqlParameter("@sub_id",subID),
                                      
                                  };
       

            return new XHCTMS.DBHELP.Common().ExcuteQuery(queryString.ToString(), paras);
        }

        /// <summary>
        /// 通过专业编号修改专业信息
        /// </summary>
        /// <param name="sub">专业编号</param>
        /// <returns>返回类型int 成功/1 失败/0 异常/-1</returns>
        public int AlterIdupSub(MODEL.Sub sub)
        {
            StringBuilder quersyString = new StringBuilder();
            quersyString.Append("update sub set sub_name=@sub_name,sub_years=@sub_years,sub_desc=@sub_dusc,sub_flag=@sub_flag where sub_id=@sub_id");
            SqlParameter[] paras ={
                                     new SqlParameter("@sub_name",SqlDbType.NVarChar,32),
                                     new SqlParameter("@sub_years",SqlDbType.Int),
                                     new SqlParameter("@sub_desc",SqlDbType.NVarChar,128),
                                     new SqlParameter("@sub_flag",SqlDbType.Bit),
                                     new SqlParameter("@sub_id",SqlDbType.Int)
                                 };
            paras[0].Value = sub.SubName;
            paras[1].Value = sub.SubYears;
            paras[2].Value = sub.SubDesc;
            paras[3].Value = sub.SubFlag;
            paras[4].Value = sub.SubId;

            return new XHCTMS.DBHELP.Common().ExcuteQuery(quersyString.ToString(), paras);
        }

        public DataTable Search(string[] parasValue ,int page =1 )
        {
            StringBuilder sb = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            string[] parasString = { " sub_name=", "sub_years=", "sub_flag=" };
            string[] parasName ={"@subName","@subYears","@subFlag"};
            List<SqlParameter> listParas = new List<SqlParameter>(); 
            sb.Append(@" select top 2 sub_id,sub_name,sub_years,sub_desc from sub ");
            sb2.Append(@" and sub_id not in
           (select top (2*@page) sub_id  from sub  ");
            bool isFirst = true;
            for(int i=0;i<parasString.Length;i++)
            {
                if(!string.IsNullOrEmpty(parasValue[i]))
                {
                    switch (isFirst)
                    {
                        case true:
                            sb.Append(" where " + parasString[i] + parasName[i]);
                            sb2.Append(" where " + parasString[i] + parasName[i]);
                            listParas.Add(new SqlParameter(parasName[i], parasValue[i]));
                            isFirst = false;
                            break;
                        case false:
                            sb.Append(" and " + parasString[i] + parasName[i]);
                            sb2.Append(" and " + parasString[i] + parasName[i]);
                            listParas.Add(new SqlParameter(parasName[i], parasValue[i]));
                            break;
                    }
                            
                }
            }
            listParas.Add(new SqlParameter("@page", page -1 ));
            sb.Append(sb2.ToString());
            sb.Append(" order by sub_name , sub_years  ) order by sub_name,sub_years");
            return new DBHELP.Common().GetDataTable(sb.ToString(),listParas);   
        }

        public string MaxPage(string[] parasValue)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select COUNT(sub_id) from sub ");
            string[] parasString = { " sub_name=", "sub_years=", "sub_flag=" };
            string[] parasName = { "@subName", "@subYears", "@subFlag" };
            List<SqlParameter> listParas = new List<SqlParameter>();
            bool isFirst =true;


            
            for (int i = 0; i < parasString.Length; i++)
            {
                if (!string.IsNullOrEmpty(parasValue[i]))
                {
                    switch (isFirst)
                    {
                        case true:
                            sb.Append(" where " + parasString[i] + parasName[i]);
                            listParas.Add(new SqlParameter(parasName[i], parasValue[i]));
                            isFirst = false;
                            break;
                        case false:
                            sb.Append(" and " + parasString[i] + parasName[i]);
                            listParas.Add(new SqlParameter(parasName[i], parasValue[i]));
                            break;
                    }
                }
                //if (!string.IsNullOrEmpty(parasValue[i]))
                //{
                //    sb.Append(" and " + parasString[i] + parasName[i]);
                //    listParas.Add(new SqlParameter(parasName[i], parasValue[i]));
                //}
            }
            return new DBHELP.Common().GetDataTable(sb.ToString(), listParas).Rows[0][0].ToString();   
        }

    }
}
